package com.zhiyou100.blog.dao.impl;

import com.zhiyou100.blog.dao.TitleDao;
import com.zhiyou100.blog.entity.Message;
import com.zhiyou100.blog.entity.Title;
import com.zhiyou100.blog.entity.User;
import com.zhiyou100.blog.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author shkstart
 * @create 2021-10-22 19:20
 */
public class TitleDaoImpl implements TitleDao {
    @Override
    public List<Title> getListTitlePaging(int startIndex, int pageSize) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT t.tid,t.tname,t.tcontent,t.tcreate_time tcreateTime,t.tguan_zhu tguanZhu,u.uid " +
                    "FROM tab_title t,tab_user u " +
                    "WHERE t.uid=u.uid " +
                    "ORDER BY t.tcreate_time DESC " +
                    "LIMIT ?,?; ";
            //ResultSetHandler<T> rsh 结果集的处理器
            //使用BeanListHandle的子类BeanHandler<T>
            BeanListHandler<Title> handler = new BeanListHandler<>(Title.class);
            List<Title> list = runner.query(conn, sql, handler, startIndex, pageSize);
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    @Override
    public Title getOneTitleById(int id) {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT tid,tname,tcontent,tcreate_time tcreateTime,tguan_zhu tguanZhu,uid " +
                    "FROM tab_title where tid=?";
            //ResultSetHandler<T> rsh 结果集的处理器
            //使用BeanListHandle的子类BeanHandler<T>
            BeanHandler<Title> handler = new BeanHandler<>(Title.class);
            return runner.query(conn, sql, handler, id);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Long getCountTitles() {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "select count(*) from tab_title;";
            //ResultSetHandler<T> rsh 结果集的处理器
            ScalarHandler<Object> handler = new ScalarHandler<>();
            return (Long) runner.query(conn, sql, handler);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0l;
    }

    @Override
    public List<Title> getListOrderByMaxAttention() {
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT t.tid,t.tname,t.tcontent,t.tcreate_time tcreateTime,t.tguan_zhu tguanZhu,u.uid " +
                    "FROM tab_title t,tab_user u " +
                    "WHERE t.uid=u.uid " +
                    "ORDER BY t.tguan_zhu DESC " +
                    "LIMIT 0,4; ";
            //ResultSetHandler<T> rsh 结果集的处理器
            //使用BeanListHandle的子类BeanHandler<T>
            BeanListHandler<Title> handler = new BeanListHandler<>(Title.class);

            List<Title> list = runner.query(conn, sql, handler);
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Title getOneByName(String tname) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT tid,tname,tcontent,tcreate_time tcreateTime,tguan_zhu tguanZhu,uid" +
                    "FROM tab_title where tid=?";
            //ResultSetHandler<T> rsh 结果集的处理器
            //使用BeanListHandle的子类BeanHandler<T>
            BeanHandler<Title> handler = new BeanHandler<>(Title.class);
            return runner.query(conn, sql, handler, tname);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int appentTitle(Title title) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            String sql = "insert into tab_title values(null,?,?,?,?,?)";
            return runner.update(conn, sql, title.getTname(), title.getTcontent()
                    , title.getTcreateTime(), title.getTguanZhu(), title.getUid());
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据问题分页查询总记录数
     *
     * @param queryText
     * @return
     */
    @Override
    public Long queryTitleQuestion(String queryText) {
        Long num = 0L;
        try (Connection conn = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            String sql1 = "select count(*) from tab_title where tname like '%" + queryText + "%';";
            String sql2 = "select count(*) from tab_title where tcontent like '%" + queryText + "%';";
            String sql3 = "SELECT count(*) FROM tab_title t1,tab_user u WHERE t1.uid=u.uid AND u.uname LIKE '%" + queryText + "%' ;";
            //ResultSetHandler<T> rsh 结果集的处理器
            ScalarHandler<Object> handler = new ScalarHandler<>();
            Long temp1 = (Long) runner.query(conn, sql1, handler);
            Long temp2 = (Long) runner.query(conn, sql2, handler);
            Long temp3 = (Long) runner.query(conn, sql3, handler);
            System.out.println("拼接查询到的记录: " + temp1 + "," + temp2 + "," + temp3);
            if (temp1 != null)
                num += temp1;
            if (temp2 != null)
                num += temp2;
            if (temp3 != null)
                num += temp3;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    @Override
    public List<Title> queryListTitleQuestion(int startIndex, String queryText) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM tab_title t1 WHERE t1.tname LIKE ").append(" '%")
                    .append(queryText).append("%' ").append("union ")
                    .append("SELECT * FROM tab_title t1 WHERE t1.tcontent LIKE ")
                    .append(" '%").append(queryText).append("%' ").append("union ")
                    .append("SELECT * FROM tab_title t1 WHERE t1.tcontent LIKE")
                    .append(" '%").append(queryText).append("%' ").append("union ")
                    .append("SELECT t1.* FROM tab_title t1,tab_user u WHERE t1.uid=u.uid AND u.uname LIKE '%")
                    .append(queryText).append("%' limit ?,3;");
            BeanListHandler<Title> beanListHandler = new BeanListHandler<>(Title.class);
            return runner.query(conn, sql.toString(), beanListHandler, startIndex);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateTitleGuanZhu(int tid) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            String sql = "update tab_title set tguan_zhu=tguan_zhu+1 where tid=?";
            return runner.update(conn, sql, tid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public long getCountTitlesByUid(int uid) {
        try (Connection conn = JDBCUtils.getConnection()) {
            QueryRunner runner = new QueryRunner();
            ScalarHandler<Object> handler = new ScalarHandler<>();
            String sql = "select count(*) from tab_title where uid=?;";
            return (long) runner.query(conn, sql, handler, uid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    @Override
    public List<Title> getListTitlePagingByUid(int startIndex, int uid) {
        try (Connection connection = JDBCUtils.getConnection();) {
            QueryRunner runner = new QueryRunner();
            BeanListHandler<Title> listHandler = new BeanListHandler<>(Title.class);
            String sql = "select tid,tname,tcontent,tcreate_time tcreateTime,tguan_zhu tguanZhu,uid from tab_title where uid=? limit ?,2";
            List<Title> list = runner.query(connection, sql, listHandler, uid, startIndex);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }


    @Test
    public void test1() {

//        System.out.println(getListTitlePagingByUid(0, 1));
//        System.out.println(getCountTitlesByUid(1));

//        System.out.println(updateTitleGuanZhu(1001));


//        System.out.println(queryTitleQuestion("一"));
//        System.out.println(getListTitlePaging(0, 5));
//        System.out.println(getOneTitleById(1001));
//        System.out.println(getCountTitles());
//        System.out.println(getListOrderByMaxAttention());
//        System.out.println(queryListTitleQuestion(1, "一"));
       /* String queryText = "一";
        StringBuilder sql = new StringBuilder();
        sql.append("ELECT * FROM tab_title t1 WHERE t1.tname LIKE").append(" '%")
                .append(queryText).append("%' ").append("union ")
                .append("SELECT * FROM tab_title t1 WHERE t1.tcontent LIKE ")
                .append(" '%").append(queryText).append("%' ").append("union ")
                .append("SELECT * FROM tab_title t1 WHERE t1.tcontent LIKE")
                .append(" '%").append(queryText).append("%' ").append("union ")
                .append("SELECT t1.* FROM tab_title t1,tab_user u WHERE t1.uid=u.uid AND u.uname LIKE '%")
                .append(queryText).append("%' ;");
        System.out.println(sql);*/
    }
}
